﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;

namespace QualityRpt.Dao.Db2
{
    public class DeviceSupplierDao:IDeviceSupplierDao
    {
        const string cachePrefix = "DSPL-";
        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                 ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.DEVICE_SUPPLIER(SUPPLIER_NAME,SUPPLIER_SHORTNAME) VALUES(?,?)",
                    ODBCHelper.CreateInputParameter("SUPPLIER_NAME", OdbcType.VarChar, 20, row["SUPPLIER_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SUPPLIER_SHORTNAME", OdbcType.VarChar, 20, row["SUPPLIER_SHORTNAME"].ToString()));
                 rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.DEVICE_SUPPLIER SET SUPPLIER_NAME=?,SUPPLIER_SHORTNAME=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("SUPPLIER_NAME", OdbcType.VarChar, 20, row["SUPPLIER_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SUPPLIER_SHORTNAME", OdbcType.VarChar, 20, row["SUPPLIER_SHORTNAME"].ToString()),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, Convert.ToInt32(row["ID"])));
            }
            DataTable rtTable = GetList("ID=" + row["ID"].ToString());
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public DataRow Get(long id)
        {
            DataTable rtTable = GetList("ID="+id.ToString());
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.DEVICE_SUPPLIER WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
        }

        public DataTable GetList(string where)
        {
            return GetList(where, "ID ASC");
        }

        public DataTable GetList(string where, string sort)
        {
            DataTable rtTable = null;
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.DEVICE_SUPPLIER ");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataTable GetPageList(Utils.PageUtils pu, string where, string sort)
        {
            throw new NotImplementedException();
        }

        public DataTable GetListSupplierName(string where, string sort, int Suppliername)
        {
            StringBuilder sbText = new StringBuilder();
            if (Suppliername==1)//1为标签厂商
            {
                sbText.Append("SELECT A.ID, SUPPLIER_NAME , SUPPLIER_SHORTNAME FROM RPT.DEVICE_SUPPLIER AS A  ");
                sbText.Append(" INNER  JOIN  RPT.DEVICE_SPEC AS B ON A.ID=B.SUPPLIER_ID");
                sbText.Append(" INNER JOIN  RPT.DEVICE AS C ON B.DEVICE_ID=C.ID");
                sbText.Append(" WHERE C.ID=1");
                sbText.Append(" GROUP BY (A.ID,SUPPLIER_NAME ,SUPPLIER_SHORTNAME )");
            }
            else if (Suppliername==2)//2为IC卡厂商
            {
                sbText.Append("SELECT A.ID, SUPPLIER_NAME , SUPPLIER_SHORTNAME FROM RPT.DEVICE_SUPPLIER AS A  ");
                sbText.Append(" INNER  JOIN  RPT.DEVICE_SPEC AS B ON A.ID=B.SUPPLIER_ID");
                sbText.Append(" INNER JOIN  RPT.DEVICE AS C ON B.DEVICE_ID=C.ID");
                sbText.Append(" WHERE C.ID=2");
                sbText.Append(" GROUP BY (A.ID,SUPPLIER_NAME ,SUPPLIER_SHORTNAME )");
            }
            else if (Suppliername==3)//3为天线厂商
            {
                sbText.Append("SELECT A.ID, SUPPLIER_NAME , SUPPLIER_SHORTNAME FROM RPT.DEVICE_SUPPLIER AS A  ");
                sbText.Append(" INNER  JOIN  RPT.DEVICE_SPEC AS B ON A.ID=B.SUPPLIER_ID");
                sbText.Append(" INNER JOIN  RPT.DEVICE AS C ON B.DEVICE_ID=C.ID");
                sbText.Append(" WHERE C.ID=3");
                sbText.Append(" GROUP BY (A.ID,SUPPLIER_NAME ,SUPPLIER_SHORTNAME )");
            }
            
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim()== string.Empty)
            {
                sbText.Append(" ORDER BY ID ASC");
            }
            DataTable rtTable = null;
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataRow GetByName(string name)
        {
            DataRow rtRow = null;
            string where = "SUPPLIER_NAME='" + name + "'";
            DataTable rtTable = GetList(where);
            if (rtTable.Rows.Count > 0)
            {
                rtRow = rtTable.Rows[0];
            }
            return rtRow;
        }
    }
}
